create database CrimeFile
go
use CrimeFile
go

----------------------------------------- TABLE -----------------------------------------


--
--1. Table structure for table 'Account'
--

create table [tblAccount]
(
	[IDAcc] int IDENTITY(1,1) primary key,
	[AccType] varchar(20) NOT NULL,
	[Descr] varchar(Max),
	[DateAcc] datetime check (DateAcc <= getdate())
)
go

--
--2. Table structure for table 'User'
--

create table [tblUser]
(
	[Username] char(20) primary key,
	[IDAcc] int references tblAccount(IDAcc),
	[Password] char(20) NOT NULL,
	[PersonalID] varchar(15) NOT NULL unique,	
	[PhoneU] varchar(13),
	[Email] varchar(30),
	[Status] int default 0,
	[DateU] datetime check (DateU <= getdate())
)
go

--
--3. Table structure for table 'Complaint Type'
--

create table [tblComplaintType]
(
	[IDCpT] int IDENTITY(1,1) primary key,
	[ComplaintT] varchar(30) NOT NULL,
	[Descr] varchar(Max),	
)
go

--
--4. Table structure for table 'Informant'
--

create table [tblInformant]
(
	[IDInformant] int IDENTITY(1,1) primary key,
	[FullName] varchar(30) NOT NULL,
	[Address] varchar(50) NOT NULL,
	[Phone] varchar(13),
	[Email] varchar(30),
	[PersonalID] varchar(15) NOT NULL,	
	[Nationality] varchar(30),
	[PassportNo] varchar(30),
	[DateI] datetime NOT NULL check(DateI <= getdate())
)
--
--5. Table structure for table 'Complaint'
--
go
create table [tblComplaint]
(
	[IDComplaint] int IDENTITY(1,1) primary key,
	[Username] char(20) references tblUser(Username),
	[IDCpT] int references tblComplaintType(IDCpT),
	[IDInformant] int references tblInformant(IDInformant),
	[Description] varchar(Max) NOT NULL,
	[SuspectName] varchar(20),
	[DetailofDefendant] text,
	[Status] int default 0,
	[DateCt] datetime check(DateCt <= getdate())
)

go

--
--6. Table structure for table 'CaseRecord'
--

create table [tblCaseRecord]
(
	[IDCR] int IDENTITY(1,1) primary key,
	[Username] char(20) references tblUser(Username),
	[CaseName] varchar(50) NOT NULL,
	[Evidence] text NOT NULL,
	[CaseStart] date,
	[CaseEnd] date,
	[CaseStatus] int default 0,
	[DateCR] datetime check(DateCR <= getdate()),
	check(CaseStart <= CaseEnd)
)
go
--
--7. Table structure for table 'PostMotern'
--

create table [tblPostMoternRecord]
(
	[IDPM] int IDENTITY(1,1) primary key,
	[IDCR] int references tblCaseRecord(IDCR),
	[FullName] varchar(30) NOT NULL,
	[Age] int check(Age >= 0),
	[Address] varchar(50) NULL,
	[PersonalID] varchar(15) NOT NULL,
	[DateOfFound] datetime NOT NULL,
	[DateOfDeath] datetime NOT NULL,
	[CaseDescription] varchar(Max) NOT NULL,
	[MurderWeapon] varchar(50),
	[DoctorName] varchar(30),
	[RalativeInfo] text,
	[DatePM] datetime check(DatePM <= getdate()),
	check (DateOfFound <= DateOfDeath)
)

go
--
--8. Table structure for table 'Report'
--
create table [tblReport]
(
	[IDIR] int IDENTITY(1,1) primary key,
	[Link] varchar(500),
	[Description] varchar(Max),
	[DateR] datetime check(DateR <= getdate())
)
go
--
--9. Table structure for table 'DetentionZone'
--

create table [tblDetentionZone]
(
	[IDDZ] int IDENTITY(1,1) primary key,
	[Zone] varchar(15) NOT NULL,
	[Desc] varchar(Max)
)
go

--
--10. Table structure for table 'DetentionRoom'
--

create table [tblDetentionRoom]
(
	[IDDR] int IDENTITY(1,1) primary key,
	[IDDZ] int references [tblDetentionZone](IDDZ),
	[Room] varchar(15) NOT NULL,
	[Desc] varchar(Max)
)

--
--11. Table structure for table 'CriminalType'
--
create table [tblCriminalType]
(
	[IDCT] int IDENTITY(1,1) primary key,
	[CriType] varchar(30) NOT NULL,
	[Descr] varchar(Max)
)
go

--
--12 Table structure for table 'Criminal'
--

create table [tblCriminal]
(
	[IDCriminal] int IDENTITY(1,1) primary key,
	[IDCR] int references tblCaseRecord(IDCR),
	[IDCT] int references tblCriminalType(IDCT),	
	[FullName] varchar(30) NOT NULL,
	[NickName] varchar(30),
	[Birth] date NOT NULL,
	[PresentAddr] varchar(50) NOT NULL,
	[PermanentAddr] varchar(50) NOT NULL,
	[PersonalID] varchar(15) NOT NULL,
	[Nationality] varchar(15),
	[PassportNo] varchar(15),
	[Occupation] varchar(30),
	[WeightC] int check(WeightC > 0),
	[HeightC] int check(HeightC > 0),
	[IdentMark] varchar(50) NOT NULL,
	[FingerPri] varchar(500) NOT NULL,
	[Image] varchar(500) NOT NULL,
	[DateCri] datetime check(DateCri <= getdate())
)
go
--
--13. Table structure for table 'PrisonRecord'
--

create table [tblPrisonRecord]
(
	[IDPrisoner] int IDENTITY(1,1) primary key,
	[IDDR] int references tblDetentionRoom(IDDR),
	[IDCriminal] int references tblCriminal(IDCriminal),
	[OfficialPTime] varchar(20) NOT NULL,
	[TempStartTime] datetime,
	[TempEndTime] datetime,
	[OfficialStartTime] datetime,
	[OfficialEndTime] datetime,
	[DatePR] datetime,
	check(TempStartTime <= TempEndTime),
	check(OfficialStartTime <= OfficialEndTime)
)
go

--
--14. Table structure for table 'CrimeType'
--

create table [tblCrimeType]
(
	[IDCrimeT] int IDENTITY(1,1) primary key,
	[CrimeType] varchar(30) NOT NULL,
	[Descr] varchar(Max)
)
go

--
--15. Table structure for table 'HistorySheet'
--
create table [tblHistorySheet]
(
	[IDHS] int IDENTITY(1,1) primary key,	
	[IDCriminal] int references tblCriminal(IDCriminal),
	[IDCrimeT] int references [tblCrimeType](IDCrimeT),
	[DateOfOcc] date check(DateOfOcc <= getdate()),
	[PlaceOfOcc] varchar(50),
	[BriefOfCase] varchar(Max),
	[DateHS] datetime check(DateHS <= getdate())
)

